/*
 * Hibiscus is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published
 * by the Free Software Foundation; either version 3 of the License,
 * or (at your option) any later version.
 *
 * Hibiscus is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Hibiscus; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
 * USA
 *
 * Copyright (C) 2003 Tetete - http://tetete.hu.
 */
package hu.tetete.hibiscus.dal;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import hu.tetete.hibiscus.model.Property;
import hu.tetete.hibiscus.model.Taxon;
import hu.tetete.hibiscus.model.TaxonLevel;
import hu.tetete.hibiscus.propertyvalue.PropertyValue;
import hu.tetete.hibiscus.util.EnumUtil;

/**
 * TODO javadoc.
 */
public class TaxonDao {

    private static final String TAG = TaxonDao.class.getName();

    /**
     * The class contains the Taxon table definitions.
     */
    public static class TaxonDef {

        /** The name of the database table which stores taxons. */
        public static final String TABLE_NAME = "taxon";

        /** The columns of the database table. */
        public enum TABLE_COLUMNS {
            /** The id column which stores a unique identifier for each property. */
            ID,
            /** The name column which stores the name of the taxon. */
            NAME,
            /** The science name column which stores the science name of the taxon. */
            SCIENCE_NAME,
            /** The {@link Enum#ordinal() ordinal number} of {@link TaxonLevel}. */
            TAXON_LEVEL_ID,
            /** The filename of the main photo of the taxon */
            PHOTO,
            /** The id of the parent taxon. */
            PARENT_ID
        }

        /** SQL DDL script which crates the taxon database table. */
        static final String TABLE_CREATE =
            "CREATE TABLE " + TaxonDef.TABLE_NAME + " (" +
                " " + TaxonDef.TABLE_COLUMNS.ID             + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                " " + TaxonDef.TABLE_COLUMNS.NAME           + " TEXT," +
                " " + TaxonDef.TABLE_COLUMNS.SCIENCE_NAME   + " TEXT," +
                " " + TaxonDef.TABLE_COLUMNS.TAXON_LEVEL_ID + " INTEGER," +
                " " + TaxonDef.TABLE_COLUMNS.PHOTO          + " TEXT," +
                " " + TaxonDef.TABLE_COLUMNS.PARENT_ID      + " INTEGER" +
            ");";
    }

    /** TODO javadoc. */
    public static class TaxonPropertyValueDef {

        /** The name of the database table which stores taxons' property values. */
        public static final String TABLE_NAME = "taxon_property";

        /** The columns of the database table. */
        public enum TABLE_COLUMNS {
            /** TODO javadoc. */
            TAXON_ID,
            /** TODO javadoc. */
            PROPERTY_ID,
            /** TODO javadoc. */
            VALUE
        }

        /** TODO javadoc. */
        static final String TABLE_CREATE =
            "CREATE TABLE " + TABLE_NAME + " (" +
                " " + TABLE_COLUMNS.TAXON_ID    + " INTEGER," +
                " " + TABLE_COLUMNS.PROPERTY_ID + " INTEGER," +
                " " + TABLE_COLUMNS.VALUE       + " TEXT," +
            " PRIMARY KEY (" + TABLE_COLUMNS.TAXON_ID + ", " + TABLE_COLUMNS.PROPERTY_ID + "));";

    }

    /**
     * Generates the needed tables and data.
     * @param db The database
     */
    public static void generateDb(final SQLiteDatabase db) {
        Log.i(TAG, "Create database: taxon");
        db.execSQL(TaxonDef.TABLE_CREATE);
        db.execSQL(TaxonPropertyValueDef.TABLE_CREATE);
    }

    /**
     * Upgrades the database. TODO javadoc.
     * @param db The database
     */
    public static void upgradeDb(final SQLiteDatabase db) {
        db.execSQL("DROP TABLE IF EXISTS " + TaxonDef.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + TaxonPropertyValueDef.TABLE_NAME);
        generateDb(db);
    }


    private final TaxonDatabaseOpenHelper databaseOpenHelper;
    private final PropertyDao             propertyDao;

    /**
     * @param databaseOpenHelper The database {@link SQLiteOpenHelper open helper}
     */
    public TaxonDao(final TaxonDatabaseOpenHelper databaseOpenHelper) {
        this.databaseOpenHelper = databaseOpenHelper;
        this.propertyDao = new PropertyDao(databaseOpenHelper);
    }

    /**
     * TODO javadoc.
     * @param id The identifier of the taxon
     * @return The taxon object
     */
    public List<Taxon> getChildrenOfTaxon(final Long id) {
        final Cursor taxonCursor = this.databaseOpenHelper.getChildrenOfTaxon(id);
        try {
            final List<Taxon> results = new ArrayList<Taxon>(taxonCursor.getCount());
            taxonCursor.moveToFirst();
            while (!taxonCursor.isAfterLast()) {
                Log.i(TAG, "I found a child for " + id);
                results.add(populateObject(taxonCursor));
                taxonCursor.moveToNext();
            }
            return results;

        } finally {
            taxonCursor.close();
        }
    }

    private Taxon populateObject(final Cursor cursor) {
        final Long       id          = Long.valueOf(cursor.getLong(TaxonDef.TABLE_COLUMNS.ID.ordinal()));
        final String     name        = cursor.getString(TaxonDef.TABLE_COLUMNS.NAME.ordinal());
        final String     scienceName = cursor.getString(TaxonDef.TABLE_COLUMNS.SCIENCE_NAME.ordinal());
        final TaxonLevel taxonLevel  = TaxonLevel.getTaxonLevel(cursor.getInt(TaxonDef.TABLE_COLUMNS.TAXON_LEVEL_ID.ordinal()));
        final String     photo       = cursor.getString(TaxonDef.TABLE_COLUMNS.PHOTO.ordinal());
        final Long       parentId    = cursor.isNull(TaxonDef.TABLE_COLUMNS.PARENT_ID.ordinal()) ? null : Long.valueOf(cursor.getLong(TaxonDef.TABLE_COLUMNS.PARENT_ID.ordinal()));
        Log.i(TAG, "Populate Taxon Id: " + id + " name: " + name);
        return new Taxon(id, name, scienceName, taxonLevel, photo, parentId);
    }

    /**
     * Saves the taxon and returns the generated id.
     * @param id The id of the taxon (if exists) (Nullable)
     * @param name The name of the taxon (NonNull)
     * @param scienceName The science name of the taxon
     * @param parent The parent of the taxon (NonNull)
     * @param taxonLevel The level of the taxon (NonNull)
     * @param propertyValues The property values (NonNull)
     * @return Generated id.
     */
    public Long saveTaxon(final Long id, final String name, final String scienceName, final Taxon parent, final TaxonLevel taxonLevel, final HashMap<Long, PropertyValue> propertyValues) {
        final Long resultId;
        if (id != null) {
            Log.d(TAG, "Update taxon: " + name);
            this.databaseOpenHelper.getWritableDatabase().update(TaxonDef.TABLE_NAME,
                    createTaxonContent(name, scienceName, parent, taxonLevel),
                    TaxonDef.TABLE_COLUMNS.ID.name() + "=" + id,
                    null);

            resultId = id;
        } else {
            Log.i(TAG, "Create new taxon: " + name);
            resultId = Long.valueOf(this.databaseOpenHelper.getWritableDatabase().insert(TaxonDef.TABLE_NAME, null, createTaxonContent(name, scienceName, parent, taxonLevel)));
        }

        savePropertyValue(id, propertyValues);


        return resultId;
    }

    public void saveTaxonPhoto(final Long id, final String photo) {
        final ContentValues updateValues = new ContentValues();
        updateValues.put(TaxonDef.TABLE_COLUMNS.PHOTO.name(), photo);
        this.databaseOpenHelper.getWritableDatabase().update(TaxonDef.TABLE_NAME,
                updateValues,
                TaxonDef.TABLE_COLUMNS.ID.name() + "=" + id,
                null);
    }

    /**
     * TODO javadoc.
     * @param id TODO javadoc
     * @return TODO javadoc
     */
    public Taxon getById(final Long id) {
        if (id == null) {
            return null;
        }

        final Cursor taxonCursor = this.databaseOpenHelper.getTaxonById(id);
        try {
            return populateObject(taxonCursor);
        } finally {
            taxonCursor.close();
        }
    }

    private ContentValues createTaxonContent(final String name, final String scienceName, final TaxonLevel taxonLevel) {
        return createTaxonContent(name, scienceName, taxonLevel.getLevelId());
    }

    private ContentValues createTaxonContent(final String name, final String scienceName, final int taxonLevelId) {
        Log.i(TAG, "Added taxon: " + name + " taxonLevelId: " + taxonLevelId);
        final ContentValues insertValues = new ContentValues();
        insertValues.put(TaxonDef.TABLE_COLUMNS.NAME.name(),           name);
        insertValues.put(TaxonDef.TABLE_COLUMNS.SCIENCE_NAME.name(),   scienceName);
        insertValues.put(TaxonDef.TABLE_COLUMNS.TAXON_LEVEL_ID.name(), Integer.valueOf(taxonLevelId));
        return insertValues;
    }

    private ContentValues createTaxonContent(final String name, final String scienceName, final Taxon parent, final TaxonLevel taxonLevel) {
        return createTaxonContent(name, scienceName, parent == null ? null : parent.getId(), taxonLevel);
    }

    private ContentValues createTaxonContent(final String name, final String scienceName, final Long parentId, final TaxonLevel taxonLevel) {
        final ContentValues insertValues = createTaxonContent(name, scienceName, taxonLevel);
        if (parentId != null) {
            insertValues.put(TaxonDef.TABLE_COLUMNS.PARENT_ID.name(), parentId);
        }
        return insertValues;
    }


    /**
     * TODO javadoc.
     * @param taxonId The id of the taxon
     * @return The property values relating to the taxon
     */
    public HashMap<Long, PropertyValue> getPropertyValues(final Long taxonId) {

        final Cursor cursor = this.databaseOpenHelper.getReadableDatabase().query(TaxonPropertyValueDef.TABLE_NAME,
                                                                                    EnumUtil.getEnumNames(TaxonPropertyValueDef.TABLE_COLUMNS.class),
                                                                                    TaxonPropertyValueDef.TABLE_COLUMNS.TAXON_ID.name() + " = " + taxonId,
                                                                                    null, null, null, null);
        try {
            final HashMap<Long, PropertyValue> results = new HashMap<Long, PropertyValue>(cursor.getCount());
            cursor.moveToFirst();
            while (!cursor.isAfterLast()) {
                Log.i(TAG, "I found a value for taxon. TaxonId: " + taxonId);
                final Long   propertyId          = Long.valueOf(cursor.getLong(TaxonPropertyValueDef.TABLE_COLUMNS.PROPERTY_ID.ordinal()));
                final String propertyValueString = cursor.getString(TaxonPropertyValueDef.TABLE_COLUMNS.VALUE.ordinal());

                final Property<?>   property = this.propertyDao.getPropertyById(propertyId);
                final PropertyValue value    = property.fromDb(propertyValueString);

                results.put(propertyId, value);

                cursor.moveToNext();
            }
            return results;

        } finally {
            cursor.close();
        }


    }

    private void savePropertyValue(final Long taxonId, final HashMap<Long, PropertyValue> values) {
        final HashMap<Long, PropertyValue> oldPropertyValues = getPropertyValues(taxonId);
        final Set<Long>                    oldPropertyIds    = new HashSet<Long>(oldPropertyValues.keySet());

        for (final Map.Entry<Long, PropertyValue> value : values.entrySet()) {
            final Long          propertyId    = value.getKey();
            final PropertyValue propertyValue = value.getValue();
            final PropertyValue oldValue      = oldPropertyValues.get(propertyId);


            if (oldValue == null) {
                insertPropertyValue(taxonId, propertyId, propertyValue);
            } else {
                if (propertyValue == null) {
                    deletePropertyValue(taxonId, propertyId);
                } else if (!oldValue.equals(propertyValue)) {
                    updatePropertyValue(taxonId, propertyId, propertyValue);
                }
            }

            oldPropertyIds.remove(propertyId);
        }

        for (final Long oldPropertyId : oldPropertyIds) {
            deletePropertyValue(taxonId, oldPropertyId);
        }
    }

    private void insertPropertyValue(final Long taxonId, final Long propertyId, final PropertyValue propertyValue) {
        @SuppressWarnings("rawtypes")
        final Property property            = this.propertyDao.getPropertyById(propertyId);
        @SuppressWarnings("unchecked")
        final String   propertyValueString = property.toDb(propertyValue);

        final ContentValues contentValues = createTaxonPropertyValueContent(taxonId, propertyId, propertyValueString);
        this.databaseOpenHelper.getWritableDatabase().insert(TaxonPropertyValueDef.TABLE_NAME, null, contentValues);
    }

    private void deletePropertyValue(final Long taxonId, final Long propertyId) {
        final int deletedRows = this.databaseOpenHelper.getWritableDatabase().delete(TaxonPropertyValueDef.TABLE_NAME,
                   TaxonPropertyValueDef.TABLE_COLUMNS.TAXON_ID + "=" + taxonId + " and " +
                   TaxonPropertyValueDef.TABLE_COLUMNS.PROPERTY_ID + "=" + propertyId, null);
        Log.d(TAG, "Rows have been deleted. TaxonId: " + taxonId + " PropertyId: " + propertyId + " Numer of deleted rows: " + deletedRows);
    }

    private void updatePropertyValue(final Long taxonId, final Long propertyId, final PropertyValue propertyValue) {
        @SuppressWarnings("rawtypes")
        final Property property            = this.propertyDao.getPropertyById(propertyId);
        @SuppressWarnings("unchecked")
        final String   propertyValueString = property.toDb(propertyValue);

        final ContentValues contentValues = new ContentValues();
        contentValues.put(TaxonPropertyValueDef.TABLE_COLUMNS.VALUE.name(),       propertyValueString);
        this.databaseOpenHelper.getWritableDatabase().update(TaxonPropertyValueDef.TABLE_NAME,
                            contentValues,
                            TaxonPropertyValueDef.TABLE_COLUMNS.TAXON_ID + "=" + taxonId + " and " +
                            TaxonPropertyValueDef.TABLE_COLUMNS.PROPERTY_ID + "=" + propertyId, null);
    }

    private ContentValues createTaxonPropertyValueContent(final Long taxonId, final Long propertyId, final String propertyValueString) {
        final ContentValues insertValues = new ContentValues();
        insertValues.put(TaxonPropertyValueDef.TABLE_COLUMNS.TAXON_ID.name(),    taxonId);
        insertValues.put(TaxonPropertyValueDef.TABLE_COLUMNS.PROPERTY_ID.name(), propertyId);
        insertValues.put(TaxonPropertyValueDef.TABLE_COLUMNS.VALUE.name(),       propertyValueString);
        return insertValues;
    }

}
